from collections import defaultdict
from typing import Any

from errors import BaseAPIException
from utility.utility import json_result_reps
from .common import session_begin
from sqlalchemy import text
from sqlalchemy.orm import Session
from utility.security import get_password_hash, verify_password
from app.yg_crm.schemas import User, UserCreate, UserUpdate
from app.yg_crm.orm import User as DB_User
import re
from log import log

def getDataBySql(db: Session, sql: str, isUpdate=False, groupedBy=None):
    forbidden_pattern = re.compile("(delete|truncate)")
    if forbidden_pattern.match(sql):
        raise BaseAPIException(detail="The dangerous sql is forbidden！！")

    if isUpdate:
        print(text(sql))
        db.execute(text(sql))
        result = json_result_reps(message="OK")
    else:
        result = db.execute(text(sql))

    log.info("groupedBy:{}",groupedBy)
    if groupedBy and not isUpdate:
        result = groupJsonData(result, groupedBy)
    else:
        result = result.all()
    # 在all()之后commit
    db.commit()
    return result

def groupJsonData(data, groupedBy):
    # 获取字段名
    columns = data.keys()
    # 转换为字典列表
    data = [dict(zip(columns, row)) for row in data.all()]
    grouped_data = defaultdict(list)
    # 按'groupedBy'字段分组
    for item in data:
        grouped_data[item[groupedBy]].append(item)
    # print(columns)
    return grouped_data
